package Inventory;

import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Collections;
import java.util.Comparator;
import java.util.Date;
import java.util.List;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.h2.mvstore.DataUtils;
import org.h2.util.DateTimeUtils;

import com.sun.org.apache.xerces.internal.impl.xpath.regex.ParseException;

public class RemindExpiry extends HttpServlet {
	


	public int daysBetween(Date smdate,Date bdate) throws ParseException, Throwable    
    {    
        SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd");  
        smdate=sdf.parse(sdf.format(smdate));  
        bdate=sdf.parse(sdf.format(bdate));  
        Calendar cal = Calendar.getInstance();    
        cal.setTime(smdate);    
        long time1 = cal.getTimeInMillis();                 
        cal.setTime(bdate);    
        long time2 = cal.getTimeInMillis();
        long between_days = 0;
      	between_days=(time2-time1)/(1000*3600*24);	
          
            
       return Integer.parseInt(String.valueOf(between_days));           
    }    	
	
	
   public Date addDays(Date date, int days)
    {
        Calendar cal = Calendar.getInstance();
        cal.setTime(date);
        cal.add(Calendar.DATE, days); //minus number would decrement the days
        return cal.getTime();
    }
   
   
	protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
		List<ExpiryRec> item = new ArrayList<ExpiryRec>();

		try {
			Class.forName("org.h2.Driver");
			Connection con = DriverManager.getConnection("jdbc:h2:~/retail","sa", "");
			PreparedStatement ps = con.prepareStatement("SELECT * FROM INVENTORY");
			ResultSet rs = ps.executeQuery();
			
			PreparedStatement ps2 = con.prepareStatement("SELECT PRODUCTID, VALID_DATE FROM PRODUCT WHERE PRODUCTID = ?");
			ResultSet rs2 = null; 
					
											
			while (rs.next()) {
				ps2.setString(1, rs.getString("PID"));
				
				rs2 =  ps2.executeQuery();
				
				if(rs2.next()) {
						ExpiryRec rec = new ExpiryRec();
						rec.setSn(rs.getInt("SN"));
						rec.setPid(rs.getString("PID"));
						rec.setPquan(rs.getInt("PQUANTITY"));
						rec.setEntrydate(rs.getTimestamp("PENTRYDATE"));
						
						Date tmpdt = rs.getTimestamp("PENTRYDATE");						
						
						rec.setExpiryDay(
								daysBetween(new Date(),addDays(tmpdt,(int)rs2.getDouble("VALID_DATE")))
								);
						item.add(rec);					
				}	
			} 
			
			rs.close();
			rs2.close();
			ps2.close();
			ps.close();
			con.close();
			
			
			
		}catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (ParseException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (Throwable e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	
		
		Collections.sort(item, new Comparator<ExpiryRec>() {
            public int compare(ExpiryRec o1, ExpiryRec o2) {
            	return o1.getExpiryDay()-o2.getExpiryDay();
            }
        }); 
		
		req.setAttribute("b", item);
		req.getRequestDispatcher("Inventory/RemindExpiry.jsp").forward(req, resp);		

		
	}

	protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
		
	}

}
